'''
分组查询:   GROUP BY
    语法:
        SELECT      GROUP FUNCTION(),列(要求出现在 group by 后面)
        FROM        表
        [WHERE       条件]
        [GROUP BY   分组]
        [ORDER BY   升序,降序]
                                分组后再进行 筛选 用 HAVING 连接  案例4

    特点:
        一.分组查询中的筛选分两类
            分组前筛选       原始表               group by 子句的前面          WHERE
            分组后筛选       分组后的结果表        group by 子句的后面          HAVING

    ORDER BY 语句可以放在这个的后面,进行排序

'''

import pymysql
conn = pymysql.connect(
    host='localhost',
    user='root',
    passwd='123456',
    db='db_library',
    port=3306,
    charset='utf8'
)
cur = conn.cursor()

# 案例1.查询每个年龄的最高工资
# w = 'SELECT MAx(salary),age FROM test GROUP BY age'
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)
# conn.commit()

# 案例2.查询每个部门有多少人
# w = 'SELECT COUNT(*),id FROM test GROUP BY id'
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)
# conn.commit()

# 条件查询:
# 案例3.查询名字包含 是 的,每个部门的最高工资
# w = "SELECT MAX(salary),COUNT(*),id FROM test WHERE name LIKE '%是%' GROUP BY id"
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)
# conn.commit()


# 添加比较复杂的筛选条件
# 案例4: 查询那个id 的人员个数>3         分组后再进行 筛选 用 HAVING 连接
# w = "SELECT COUNT(*),id FROM test GROUP BY id HAVING COUNT(*)>3"
# cur.execute(w)
# ret = cur.fetchall()
# print(ret)
# conn.commit()




















